﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DomainModel;  // must add this...
using System.Configuration; // must add this...
using System.Data.SqlClient; // must add this...
using System.Data; // must add this...


namespace DAL
{
    public static class DALTv
    {
        static string connection_string = ConfigurationSettings.AppSettings["dsn"];

 
        /****************PRODUCT PROCS******************/

        //gets the display types of the product
        public static Dictionary<int, string> GetDisplay(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            Dictionary<int, string> all_displays = new Dictionary<int, string>();
            try
            {
                string strSQL = "get_Display";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return null;

                int id = 0;
                string type = "";
                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    id = Convert.ToInt32(myDS.Tables[0].Rows[i]["display_id"].ToString());
                    type = myDS.Tables[0].Rows[i]["display_type"].ToString();
                    all_displays.Add(id, type);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return all_displays;
        }

        //gets the make of the product
        public static Dictionary<int, string> GetMake(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            Dictionary<int, string> all_makes = new Dictionary<int, string>();
            try
            {
                string strSQL = "get_Make";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                int id = 0;
                string type = "";
                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    id = Convert.ToInt32(myDS.Tables[0].Rows[i]["make_id"].ToString());
                    type = myDS.Tables[0].Rows[i]["make"].ToString();
                    all_makes.Add(id, type);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return all_makes;
        }

        //gets the refresh rates of the products
        public static Dictionary<int, int> GetRefresh(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            Dictionary<int, int> all_refresh = new Dictionary<int, int>();
            try
            {
                string strSQL = "get_Refresh";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                int id = 0;
                int rate = 0;
                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    id = Convert.ToInt32(myDS.Tables[0].Rows[i]["refresh_id"].ToString());
                    rate = Convert.ToInt32(myDS.Tables[0].Rows[i]["refresh_rate"].ToString());
                    all_refresh.Add(id, rate);
                }
            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return all_refresh;
        }

        //gets the resolutions of the products
        public static Dictionary<int, int> GetResolution(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            Dictionary<int, int> all_resolutions = new Dictionary<int, int>();
            try
            {
                string strSQL = "get_Resolution";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                int id = 0;
                int res = 0;
                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    id = Convert.ToInt32(myDS.Tables[0].Rows[i]["res_id"].ToString());
                    res = Convert.ToInt32(myDS.Tables[0].Rows[i]["resolution"].ToString());
                    all_resolutions.Add(id, res);
                }
            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return all_resolutions;
        }

        //gets the sizes of the products
        public static Dictionary<int, int> GetSize(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            Dictionary<int, int> all_sizes = new Dictionary<int, int>();
            try
            {
                string strSQL = "get_Size";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                int id = 0;
                int siz = 0;
                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    id = Convert.ToInt32(myDS.Tables[0].Rows[i]["size_id"].ToString());
                    siz = Convert.ToInt32(myDS.Tables[0].Rows[i]["size"].ToString());
                    all_sizes.Add(id, siz);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return all_sizes;
        }

        //gets the price of a product
        public static double GetProductPrice(int id, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            double price = 0;
            try
            {
                string strSQL = "get_product_price";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@product_id", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@product_id"].Value = id;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                price = Convert.ToDouble(myDS.Tables[0].Rows[0]["price"]);

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return price;
        }


        //this procedure returns the item searched for
        public static List<Product> SearchItem(int display, int make, int size, ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            List<Product> itemlist = new List<Product>();
            Product prod = null;
            try
            {
                string strSQL = "search_item";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@display_type", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@make", SqlDbType.Int));
                mySA.SelectCommand.Parameters.Add(new SqlParameter("@size", SqlDbType.Int));

                mySA.SelectCommand.Parameters["@display_type"].Value = display;
                mySA.SelectCommand.Parameters["@make"].Value = make;
                mySA.SelectCommand.Parameters["@size"].Value = size;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return null;

                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    prod = new Product();
                    prod.product_id = Convert.ToInt32(myDS.Tables[0].Rows[i]["product_id"]);
                    prod.display_type = Convert.ToString(myDS.Tables[0].Rows[i]["display_type"]);
                    prod.make_type = Convert.ToString(myDS.Tables[0].Rows[i]["make"]);
                    prod.size_type = Convert.ToString(myDS.Tables[0].Rows[i]["size"]);
                    prod.res_type = Convert.ToString(myDS.Tables[0].Rows[i]["resolution"]);
                    prod.refresh_type = Convert.ToString(myDS.Tables[0].Rows[i]["refresh_rate"]);
                    prod.price = Convert.ToDouble(myDS.Tables[0].Rows[i]["price"]);
                    prod.shipping_type = Convert.ToString(myDS.Tables[0].Rows[i]["shipping"]);
                    itemlist.Add(prod);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return itemlist;
        }

        /* DAL method for partial view pop item and our of stock*/


        public static int numberOutOfStock(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            int total_num = 0;

            try
            {
                string strSQL = "get_out0fStock";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return 0;


                total_num = Convert.ToInt32(myDS.Tables[0].Rows[0]["outofstock"]);


            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }

            return total_num;
        }

      

        public static List<Product> GetTopFive(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            List<Product> itemlist = new List<Product>();
            Product prod = null;
            try
            {
                string strSQL = "get_top_five";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return null;

                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    prod = new Product();
                    prod.product_id = Convert.ToInt32(myDS.Tables[0].Rows[i]["product_id"]);
                    prod.display_type =  DALTool.DisplayToString( Convert.ToString(myDS.Tables[0].Rows[i]["display_id"]));
                    prod.make_type = DALTool.MakeToString( Convert.ToString(myDS.Tables[0].Rows[i]["make_id"]));
                    prod.size_type = DALTool.SizeToString( Convert.ToString(myDS.Tables[0].Rows[i]["size_id"]));
                    prod.res_type = DALTool.ResToString( Convert.ToString(myDS.Tables[0].Rows[i]["res_id"]));
                    prod.refresh_type = DALTool.RrToString( Convert.ToString(myDS.Tables[0].Rows[i]["refresh_id"]));
                    prod.price = Convert.ToDouble(myDS.Tables[0].Rows[i]["price"]);
                    prod.shipping_type = DALTool.ShipToString( Convert.ToString(myDS.Tables[0].Rows[i]["shipping"]));
                    prod.num_sales = Convert.ToInt32(myDS.Tables[0].Rows[i]["num_sales"]);
                    prod.num_stocked = Convert.ToInt32(myDS.Tables[0].Rows[i]["num_stocked"]);

                    itemlist.Add(prod);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return itemlist;
        }


        public static List<Product> GetOutStockDetail(ref List<string> errors)
        {
            SqlConnection conn = new SqlConnection(connection_string);
            List<Product> itemlist = new List<Product>();
            Product prod = null;
            try
            {
                string strSQL = "get_out0fStock_detail";

                SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
                mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

                DataSet myDS = new DataSet();
                mySA.Fill(myDS);

                if (myDS.Tables[0].Rows.Count == 0)
                    return null;

                for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
                {
                    prod = new Product();
                    prod.product_id = Convert.ToInt32(myDS.Tables[0].Rows[i]["product_id"]);
                    prod.display_type =  DALTool.DisplayToString( Convert.ToString(myDS.Tables[0].Rows[i]["display_id"]));
                    prod.make_type = DALTool.MakeToString( Convert.ToString(myDS.Tables[0].Rows[i]["make_id"]));
                    prod.size_type = DALTool.SizeToString( Convert.ToString(myDS.Tables[0].Rows[i]["size_id"]));
                    prod.res_type = DALTool.ResToString( Convert.ToString(myDS.Tables[0].Rows[i]["res_id"]));
                    prod.refresh_type = DALTool.RrToString( Convert.ToString(myDS.Tables[0].Rows[i]["refresh_id"]));
                    prod.price = Convert.ToDouble(myDS.Tables[0].Rows[i]["price"]);
                    prod.shipping_type = DALTool.ShipToString( Convert.ToString(myDS.Tables[0].Rows[i]["shipping"]));
                    prod.num_sales = Convert.ToInt32(myDS.Tables[0].Rows[i]["num_sales"]);
                    prod.num_stocked = Convert.ToInt32(myDS.Tables[0].Rows[i]["num_stocked"]);

                    itemlist.Add(prod);
                }

            }
            catch (Exception e)
            {
                errors.Add("Error: " + e.ToString());
            }
            finally
            {
                conn.Dispose();
                conn = null;
            }
            return itemlist;
        }


    }
}
